﻿using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Vess.NPOI.Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelLeverCascading();
            Console.WriteLine("完成");
            Console.ReadKey();
        }

        static List<DataEntity> DEntitys()
        {
            List<DataEntity> result = new List<DataEntity>()
            {
                new DataEntity()
                {
                    name ="江苏",
                    child =new List<DataEntity>()
                    {
                        new DataEntity()
                        {
                            name = "南京",
                            child = new List<DataEntity>()
                            {
                                new DataEntity(){ name = "鼓楼区" },
                                new DataEntity(){ name = "玄武区" }
                            }
                        },
                        new DataEntity()
                        {
                            name = "苏州",
                            child = new List<DataEntity>()
                            {
                                new DataEntity(){ name = "吴中区" },
                                new DataEntity(){ name = "姑苏区" },
                                new DataEntity(){ name = "相城区" }
                            }
                        },
                        new DataEntity()
                        {
                            name = "无锡",
                            child = new List<DataEntity>()
                            {
                                new DataEntity(){ name = "梁溪区" }
                            }
                        },
                    }
                },
                new DataEntity()
                {
                    name ="山东",
                    child =new List<DataEntity>()
                    {
                        new DataEntity()
                        {
                            name = "济南",
                            child = new List<DataEntity>()
                            {
                                new DataEntity(){ name = "历下区" },
                                new DataEntity(){ name = "市中区" }
                            }
                        }
                    }
                },
                new DataEntity()
                {
                    name ="浙江",
                    child = new List<DataEntity>()
                    {
                        new DataEntity()
                        {
                            name = "杭州",
                            child = new List<DataEntity>()
                            {
                                new DataEntity(){ name = "上城区" },
                                new DataEntity(){ name = "下城区" },
                                new DataEntity(){ name = "萧山区" }
                            }
                        },
                        new DataEntity()
                        {
                            name = "宁波",
                            child = new List<DataEntity>()
                            {
                                new DataEntity(){ name = "江北区" }
                            }
                        }
                    }
                }
            };
            return result;
        }

        /// <summary>
        /// 创建模板数据表
        /// 根据模板数据表，建立EXCEL级联
        /// 导出到EXCEL
        /// </summary>
        static void ExcelLeverCascading()
        {
            //获取数据源，就当是从数据库或其它地方取出来的
            List<DataEntity> model = DEntitys();
            //创建工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();

            //创建需要使用级联关系的表格
            ISheet useSheet = workbook.CreateSheet("Using");
            //创建表头
            IRow rowHead = useSheet.CreateRow(0);
            rowHead.CreateCell(0).SetCellValue("省");
            rowHead.CreateCell(1).SetCellValue("市");
            rowHead.CreateCell(2).SetCellValue("区");

            #region 创建数据源格式表
            string sheetName = "Type";
            //创建sheet，用于制作数据源
            ISheet typeSheet = workbook.CreateSheet(sheetName);
            //隐藏数据源表
            //开发环境时先注掉，以便随时查看数据源的效果，生产环境时打开，以便保护数据源
            //注意，需要隐藏的表，一定要最后建立，否则无法隐藏，因此这里先建立了Using表，然后再建立了Type表这个顺序一定不能反
            workbook.SetSheetHidden(workbook.GetSheetIndex(typeSheet), true);
            //行号，起始为0
            int rowNo = 0;
            //整理一级数据
            FormatData(typeSheet, model, "省市区", ref rowNo, workbook, sheetName);
            //整理二级数据
            foreach (DataEntity item in model)
            {
                FormatData(typeSheet, item.child, item.name, ref rowNo, workbook, sheetName);
            }
            //整理三级数据
            foreach (DataEntity item in model)
            {
                foreach (DataEntity child in item.child)
                {
                    FormatData(typeSheet, child.child, child.name, ref rowNo, workbook, sheetName);
                }
            }
            #endregion

            #region 创建级联关系            
            //给500行创建下拉级别关系
            int rowCount = 500;
            //最小开始列
            int minCell = 0;
            //最大结束列
            int maxCell = 0;
            //第一级制作下拉
            ExcelLevelRelation(useSheet, "省市区", 1, rowCount - 1, minCell, maxCell);
            for (int j = 1; j <= rowCount; j++)
            {
                int beginCell = minCell;
                int endCell = maxCell;
                //第二级绑定与第一级的级联关系(EXCEL中叫引用)
                ExcelLevelRelation(useSheet, string.Format("INDIRECT(${0}${1})", "A", j + 1), j, j, ++beginCell, ++endCell);
                //第三级绑定与第二级的级联关系(EXCEL中叫引用)
                ExcelLevelRelation(useSheet, string.Format("INDIRECT(${0}${1})", "B", j + 1), j, j, ++beginCell, ++endCell);
            }
            #endregion

            #region 创建EXCEL,并写入，保存
            string fileName = "测试";
            string DateTimeString = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            string filePath = string.Format("E:\\00-Work\\Vess.NPOI.Excel\\Temp\\{0}_{1}.xlsx", fileName, DateTimeString);
            string dirName = Path.GetDirectoryName(filePath);
            if (!Directory.Exists(dirName))
            {
                Directory.CreateDirectory(dirName);
            }
            using (FileStream fs = File.Create(filePath))
            {
                workbook.Write(fs);
            }
            #endregion
        }

        /// <summary>
        /// 格式化数据，并建立名称管理
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="model">数据源(数据库)</param>
        /// <param name="firstCellName">第一列的名称</param>
        /// <param name="rowNo">当前操作的行号</param>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheetName">工作表名</param>
        private static void FormatData(ISheet sheet, List<DataEntity> model, string firstCellName, ref int rowNo, XSSFWorkbook workbook, string sheetName)
        {
            //按行写入类型数据
            IRow row = sheet.CreateRow(rowNo);
            row.CreateCell(0).SetCellValue(firstCellName);
            int rowCell = 1;
            foreach (DataEntity item in model)
            {
                row.CreateCell(rowCell).SetCellValue(item.name);
                rowCell++;
            }
            //建立名称管理
            rowNo++;
            IName range = workbook.CreateName();
            range.NameName = firstCellName;
            string colName = GetExcelColumnName(model.Count + 1);
            range.RefersToFormula = string.Format("{0}!$B${1}:${2}${1}", sheetName, rowNo, colName);
            range.Comment = rowNo.ToString("00");
        }

        /// <summary>
        /// 建立级联关系
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="source">数据源(EXCEL表)</param>
        /// <param name="minRow">起始行</param>
        /// <param name="maxRow">终止行</param>
        /// <param name="minCell">起始列</param>
        /// <param name="maxCell">终止列</param>
        private static void ExcelLevelRelation(ISheet sheet, string source, int minRow, int maxRow, int minCell, int maxCell)
        {
            //第一层绑定下拉的时候，可以一次性选择多个单元格进行绑定
            //第是从第二层开始，就只能一对一的绑定，如果目标单元格要与哪一个一级单元格进行关联
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet as XSSFSheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(source);
            CellRangeAddressList cellRegions = new CellRangeAddressList(minRow, maxRow, minCell, maxCell);
            XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, cellRegions);
            validation.SuppressDropDownArrow = true;
            validation.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
            validation.ShowErrorBox = true;
            sheet.AddValidationData(validation);
        }

        /// <summary>
        /// 获取Excel列名
        /// </summary>
        /// <param name="columnNumber">列的序号，如：A、B、C、AA、BB</param>
        /// <returns></returns>
        static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;
            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }
            return columnName;
        }
    }

    /// <summary>
    /// 数据源实体
    /// </summary>
    class DataEntity
    {
        /// <summary>
        /// 名称
        /// </summary>
        internal string name { get; set; }
        /// <summary>
        /// 子级
        /// </summary>
        internal List<DataEntity> child { get; set; }
    }
}
